package o2o.task.order;

import com.jinyou.utils.common.DateUtils;
import com.jinyou.utils.common.JYMathDoubleUtils;
import com.jinyou.utils.common.ValidateUtil;
import o2o.cache.order.OrderPayMemory;
import o2o.cache.shop.ShopMemory;
import o2o.cache.wallet.UserWalletFlowMemory;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.order.ORDER_STATUS;
import o2o.dao.PO.order.ORDER_TYPE;
import o2o.dao.PO.order.OrderStatementInfo;
import o2o.dao.PO.order.OrderStatementStatistics;
import o2o.dao.VO.order.REFUND_APPLY_STATUS;
import org.apache.commons.lang.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

// 订单统计
public class OrderStatisticsUtils {


    public static void paotuiStatics(Long startTime, Long endTime) {
        paotuiStatics(startTime, endTime, false);
    }

    // 跑腿订单统计
    public static void paotuiStatics(Long startTime, Long endTime, boolean userData) {
        String dataDbName = OrderBakTaskUtils.getDataDbName();
        String tb_order_info = PojoTables.OrderInfo;
        String tb_order_pay = PojoTables.OrderPay;
        if (userData) {
            tb_order_info = dataDbName + "." + PojoTables.D_ORDER_INFO;
            tb_order_pay = dataDbName + "." + PojoTables.D_ORDER_PAY;
        }


        // 插入数据
        StringBuffer sbInsert = new StringBuffer();
        sbInsert.append("INSERT INTO ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATISTICS_PAOTUI).append("  " +
                "(oriDate,adminUsername,agentid,province,city,county,orderCounts,allPayPrice,platformMoney,postmanMoney,totalGoodsMoney,deliveryPrice,platformAwardMoney,userCount ) " +
                "SELECT  FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') AS oriDate," +
                "uar.username adminUsername,uar.agentid ,province,city,county," +
                "count(ori.id) orderCounts, SUM(IFNULL(orp.totalprice,0)) allPayPrice,SUM(IFNULL(orp.platformMoney,0)) AS platformMoney, SUM(IFNULL(orp.postmanMoney,0)) AS postmanMoney,\n" +
                "SUM(IFNULL(ori.totalGoodsMoney,0)) AS totalGoodsMoney, SUM(IFNULL(ori.deliveryPrice,0)) AS deliveryPrice, SUM(IFNULL(orp.platformAwardMoney,0)) AS platformAwardMoney, \n" +
                "COUNT(DISTINCT ori.username) userCount   ")
                .append(" FROM ").append(tb_order_info).append(" ori ")
                .append(" LEFT JOIN ").append(tb_order_pay).append(" orp ON orp.orderNo = ori.orderNo ")
                .append("LEFT JOIN " + PojoTables.USER_AGENT_RELATION + " uar ON uar.agentId=ori.agentId ")
                .append(" WHERE ori.delFlag=0 AND ori.orderType=").append(ORDER_TYPE.PAO_TUI).append("  AND orp.delFlag=0  ")
                .append(" AND ori.orderstatus=").append(ORDER_STATUS.OVER).append(" AND ori.isRefundApply!=").append(REFUND_APPLY_STATUS.PASS)
                .append(" and uar.delFlag=0  ");
        if (ValidateUtil.isAbsLong(startTime)) {
            sbInsert.append(" AND ori.createTime > ").append(startTime);
        }
        if (ValidateUtil.isAbsLong(endTime)) {
            sbInsert.append(" AND ori.createTime < ").append(endTime);
        }
        sbInsert.append(" GROUP BY oriDate,city,county,province,agentUsername ");
        try {
            UserDaoSql.getInstance().executeBySql(sbInsert.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }


        // 更新退款情况
        StringBuffer sbUpdate = new StringBuffer();
        sbUpdate.append(" UPDATE ( " +
                "SELECT uar.username adminUsername,uar.agentid ,province,city,county,\n" +
                "count(ori.id)  refundApplyCount,SUM( ori.totalprice) refundApplyMoney, \n" +
                " FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') AS oriDate  \n" +
                "FROM ").append(tb_order_info).append(" ori   \n" +
                "LEFT JOIN ").append(tb_order_pay).append(" orp ON orp.orderNo = ori.orderNo \n" +
                "LEFT JOIN ").append(PojoTables.USER_AGENT_RELATION).append(" uar ON uar.agentId=ori.agentId  \n" +
                "WHERE ori.delFlag=0 AND ori.orderType=2  AND orp.delFlag=0  \n" +
                " AND uar.delFlag=0   \n" +
                "AND (ori.isRefundApply=2 OR ori.orderStatus=4 OR ori.orderStatus=71) ");

        if (ValidateUtil.isAbsLong(startTime)) {
            sbUpdate.append(" AND ori.createTime > ").append(startTime);
        }
        if (ValidateUtil.isAbsLong(endTime)) {
            sbUpdate.append(" AND ori.createTime < ").append(endTime);
        }
        sbUpdate.append(" GROUP BY oriDate,city,county,province,agentUsername ");

        sbUpdate.append(") a,").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATISTICS_PAOTUI).append(" b\n" +
                " SET b.refundApplyCount = a.refundApplyCount,b.refundApplyMoney = a.refundApplyMoney\n" +
                "WHERE a.oridate = b.oridate AND a.adminUsername = b.adminUsername AND a.agentid = b.agentid AND a.province=b.province AND a.city = b.city AND a.county = b.county;\n");

        try {
            UserDaoSql.getInstance().executeBySql(sbUpdate.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }


        // 插入只有退款的
        StringBuffer sbRefund = new StringBuffer();
        sbRefund.append("INSERT INTO ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATISTICS_PAOTUI).append(" (\n" +
                "adminUsername,agentid,province,city,county,refundApplyCount,refundApplyMoney,oriDate\n" +
                ") " +
                "SELECT a.* FROM (\n" +
                "SELECT uar.username adminUsername,uar.agentid ,province,city,county,\n" +
                "count(ori.id)  refundApplyCount,SUM( ori.totalprice) refundApplyMoney,\n" +
                " FROM_UNIXTIME(ori.createTime/1000,'%Y-%m-%d') AS oriDate\n" +
                "FROM ").append(tb_order_info).append(" ori\n" +
                "LEFT JOIN ").append(tb_order_pay).append(" orp ON orp.orderNo = ori.orderNo\n" +
                "LEFT JOIN ").append(PojoTables.USER_AGENT_RELATION).append(" uar ON uar.agentId=ori.agentId\n" +
                "WHERE ori.delFlag=0 AND ori.orderType=2  AND orp.delFlag=0\n" +
                " AND uar.delFlag=0 " +
                "AND (ori.isRefundApply=2 OR ori.orderStatus=4 OR ori.orderStatus=71)");
        if (ValidateUtil.isAbsLong(startTime)) {
            sbRefund.append(" AND ori.createTime > ").append(startTime);
        }
        if (ValidateUtil.isAbsLong(endTime)) {
            sbRefund.append(" AND ori.createTime < ").append(endTime);
        }
        sbRefund.append(" GROUP BY oriDate,city,county,province,agentUsername\n" +
                " ) a " +
                " LEFT JOIN ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATISTICS_PAOTUI)
                .append(" b ON a.oridate = b.oridate AND a.adminUsername = b.adminUsername AND a.agentid = b.agentid AND a.province=b.province AND a.city = b.city AND a.county = b.county ")
                .append(" WHERE b.oridate IS NULL");

        try {
            UserDaoSql.getInstance().executeBySql(sbRefund.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 账单统计
     */
    public static Set<Integer> statementOrderStatistics(boolean isSysAuto, String opUser) {
        Set<Integer> failTypeSet = new HashSet<>();
        Long createTime = DateUtils.getCurrTime();
        // 1. 日结
        try {
            statementOrderByStatementType(1, isSysAuto, opUser, createTime);
        } catch (Exception e) {
            e.getMessage();
            failTypeSet.add(1);
        }
        // 3.三日结
        try {
            statementOrderByStatementType(3, isSysAuto, opUser, createTime);
        } catch (Exception e) {
            e.getMessage();
            failTypeSet.add(3);
        }
        // 周结算
        try {
            statementOrderByStatementType(7, isSysAuto, opUser, createTime);
        } catch (Exception e) {
            e.getMessage();
            failTypeSet.add(7);
        }
        // 月结算
        try {
            statementOrderByStatementType(30, isSysAuto, opUser, createTime);
        } catch (Exception e) {
            e.getMessage();
            failTypeSet.add(30);
        }
        return failTypeSet;
    }

//    public static void statementOrderByStatementType2(int type, Long createTime) throws SQLException {
//        // 日结  三日结
//        if (type == 1 || type == 3) {
//            /**
//             * SELECT
//             *   O.agentId,
//             *   O.shopId,
//             *   OP.shopMoney,
//             *   O.orderNo,
//             *   O.totalPrice,
//             *   O.deliveryPrice,
//             *   O.originalYunfei,
//             *   O.originalYunFeiShop,
//             *   OP.platformAwardMoney,
//             *   (IFNULL(OP.`cityAdminAward`,0)
//             *   +IFNULL(OP.`cityAdminAward1`,0)
//             *   +IFNULL(OP.`cityAdminAward2`,0)
//             *   +IFNULL(OP.`cityAdminAward3`,0)
//             *   +IFNULL(OP.`cityAdminAward4`,0)
//             *  ) cityAdminAwardMoney,
//             *   O.shopAwardMoney
//             * FROM
//             *   order_info AS O
//             *   LEFT JOIN order_pay OP
//             *     ON O.orderNo = OP.orderNo
//             *     AND OP.delFlag = 0
//             *   INNER JOIN
//             *     (SELECT
//             *       id AS shopId,
//             *       shopName
//             *     FROM
//             *       shop_info
//             *     WHERE delFlag = 0
//             *       AND settlementType = 1
//             *     UNION
//             *     SELECT
//             *       S.id AS shopId,
//             *       S.shopName AS shopName
//             *     FROM
//             *       shop_info AS S
//             *       LEFT JOIN shop_agent_relation AS SA
//             *         ON S.id = SA.shopId
//             *         AND SA.delFlag = 0
//             *       LEFT JOIN city_agent AS C
//             *         ON C.id = SA.agentId
//             *         AND C.delFlag = 0
//             *     WHERE S.delFlag = 0
//             *       AND S.settlementType = 0
//             *       AND C.settlementType = 1
//             * ) AS T ON O.shopId = T.shopId
//             *
//             * WHERE O.delFlag = 0
//             * AND OP.statisticState = 0
//             * AND OP.settlementState = 0
//             * AND OP.isOrderCancel = 0
//             * AND O.isRefundApply != 2
//             * AND O.orderStatus = 9 ;
//             */
//            Long lastSettlementTime = DateUtils.getTodayStartTimestamp() - 1 * 60 * 1000L; // 今天开始时间减去一分钟 作为最后结算时间
//            Long validTime = DateUtils.getTodayStartTimestamp() - type * 24 * 60 * 60 * 1000L;
//            StringBuilder shopSql = new StringBuilder();
//            shopSql.append(" SELECT id AS shopId, shopName   FROM shop_info  WHERE delFlag = 0 AND settlementType = ").append(type).append(" and  (lastSettlementTime is null or lastSettlementTime < ").append(validTime).append(")")
//                    .append(" UNION ")
//                    .append("  SELECT S.id AS shopId,  S.shopName AS shopName FROM ").append(PojoTables.ShopInfo).append(" AS S LEFT JOIN ").append(PojoTables.SHOP_AGENT_RELATION).append(" AS SA ON S.id = SA.shopId AND SA.delFlag = 0 ")
//                    .append("    LEFT JOIN city_agent AS C ").append(" ON C.id = SA.agentId  AND C.delFlag = 0 ")
//                    .append(" WHERE S.delFlag = 0  AND S.settlementType = 0 AND C.settlementType = ").append(type).append(" and  (S.lastSettlementTime is null or S.lastSettlementTime < ").append(validTime).append(")");
//            StringBuilder sql = new StringBuilder();
//            sql.append("  SELECT ")
//                    .append(" O.agentId,  O.shopId, OP.shopMoney, O.orderNo, O.totalPrice, O.deliveryPrice, O.originalYunfei, O.originalYunFeiShop, OP.platformAwardMoney,")
//                    .append(" (IFNULL(OP.`cityAdminAward`,0) +IFNULL(OP.`cityAdminAward1`,0) +IFNULL(OP.`cityAdminAward2`,0) +IFNULL(OP.`cityAdminAward3`,0) +IFNULL(OP.`cityAdminAward4`,0) ) cityAdminAwardMoney, ")
//                    .append(" (IFNULL(OP.`cityAdminMoney`, 0) + IFNULL(OP.`cityAdminMoney1`, 0) + IFNULL(OP.`cityAdminMoney2`, 0) + IFNULL(OP.`cityAdminMoney3`, 0) + IFNULL(OP.`cityAdminMoney4`, 0)) cityAdminMoney, ")
//                    .append("  OP.platformMoney, ")
//                    .append("  OP.postmanMoney, ")
//                    .append(" OP.shopAwardMoney ")
//                    .append(" FROM ")
//                    .append(PojoTables.OrderInfo).append(" as O ")
//                    .append(" LEFT JOIN ").append(PojoTables.OrderPay).append("  OP ")
//                    .append("  ON O.orderNo = OP.orderNo AND OP.delFlag = 0 ")
//                    .append(" INNER JOIN (").append(shopSql).append(") AS T ON O.shopId = T.shopId ")
//                    .append(" where O.delFlag = 0 ")
//                    .append(" AND OP.statisticState = 0 ")
//                    .append(" AND OP.settlementState = 0 ")
//                    .append(" AND OP.isOrderCancel = 0 ")
//                    .append(" AND O.isRefundApply != ").append(REFUND_APPLY_STATUS.PASS)
//                    .append(" AND (O.orderStatus = ").append(ORDER_STATUS.OVER)
//                    .append(" OR O.orderStatus = ").append(ORDER_STATUS.SHOP_FINISH)
//                    .append(" OR O.orderStatus = ").append(ORDER_STATUS.USER_SURED)
//                    .append(" OR O.orderStatus = ").append(ORDER_STATUS.POSTMAN_FINISH).append(") ")
//                    .append(" AND O.createTime < ").append(lastSettlementTime);
//
//            List<OrderStatementInfo> orderStatementInfos = null;
//            try {
//                System.out.println("统计执行sql---->" + sql.toString());
//                orderStatementInfos = (List<OrderStatementInfo>) UserDaoSql.getInstance().list(OrderStatementInfo.class, sql.toString());
//            } catch (Exception e) {
//                e.getMessage();
//                return;
//            }
//            if (ValidateUtil.isAbsList(orderStatementInfos)) {
//                collationAndCalculation(orderStatementInfos, type, lastSettlementTime, createTime, true, "admin");
//            }
//        }
//
//        // 周结算
//        if (type == 7) {
//            /**
//             * SELECT
//             *   O.agentId,
//             *   O.shopId,
//             *   OP.shopMoney,
//             *   O.orderNo,
//             *   O.totalPrice,
//             *   O.deliveryPrice,
//             *   O.originalYunfei,
//             *   O.originalYunFeiShop,
//             *   OP.platformAwardMoney,
//             *   (
//             *     IFNULL(OP.`cityAdminAward`, 0) + IFNULL(OP.`cityAdminAward1`, 0) + IFNULL(OP.`cityAdminAward2`, 0) + IFNULL(OP.`cityAdminAward3`, 0) + IFNULL(OP.`cityAdminAward4`, 0)
//             *   ) cityAdminAwardMoney,
//             *   (
//             *     IFNULL(OP.`cityAdminMoney`, 0) + IFNULL(OP.`cityAdminMoney1`, 0) + IFNULL(OP.`cityAdminMoney2`, 0) + IFNULL(OP.`cityAdminMoney3`, 0) + IFNULL(OP.`cityAdminMoney4`, 0)
//             *   ) cityAdminMoney,
//             *   OP.platformMoney,
//             *   OP.postmanMoney,
//             *   OP.shopAwardMoney
//             * FROM
//             *   order_info AS O
//             *   LEFT JOIN order_pay OP
//             *     ON O.orderNo = OP.orderNo
//             *     AND OP.delFlag = 0
//             *   INNER JOIN
//             *     ( SELECT
//             *       DISTINCT(S.id) AS shopId
//             *     FROM
//             *       shop_info AS S
//             *       LEFT JOIN shop_agent_relation AS SA
//             *         ON S.id = SA.shopId
//             *         AND SA.delFlag = 0
//             *       LEFT JOIN city_agent AS C
//             *         ON C.id = SA.agentId
//             *         AND C.delFlag = 0
//             *     WHERE S.delFlag = 0
//             *       AND (S.settlementType = 7 OR (S.settlementType =0 AND C.settlementType = 7))
//             *       AND C.settlementDay = 1
//             *       ) AS T
//             *     ON O.shopId = T.shopId
//             * WHERE O.delFlag = 0
//             *   AND OP.statisticState = 0
//             *   AND OP.settlementState = 0
//             *   AND OP.isOrderCancel = 0
//             *   AND O.isRefundApply != 2
//             *   AND (
//             *     O.orderStatus = 9
//             *     OR O.orderStatus = 29
//             *     OR O.orderStatus = 64
//             *     OR O.orderStatus = 19
//             *   )
//             *   AND O.createTime < 1606060740000
//             */
//            Calendar c = Calendar.getInstance();
//            c.setTime(new Date());
//            int weekday = c.get(Calendar.DAY_OF_WEEK);
//            StringBuilder shopSql = new StringBuilder();
//            shopSql.append("  SELECT DISTINCT(S.id) AS shopId FROM ").append(PojoTables.ShopInfo).append(" AS S LEFT JOIN ").append(PojoTables.SHOP_AGENT_RELATION).append(" AS SA ON S.id = SA.shopId AND SA.delFlag = 0 ")
//                    .append("    LEFT JOIN city_agent AS C ").append(" ON C.id = SA.agentId  AND C.delFlag = 0 ")
//                    .append(" WHERE S.delFlag = 0   AND (S.settlementType = ").append(type).append(" OR (S.settlementType =0 AND C.settlementType = ").append(type).append(")) ")
//                    .append(" AND C.settlementDay = ").append(weekday);
//
//
//        }
//
//
//        // 月结
//        if (type == 30) {
//
//        }
//
//
//    }

    public static void statementOrderByStatementType(int type, boolean isSysAuto, String opUser, Long createTime) throws SQLException {
        Long lastSettlementTime = DateUtils.getTodayStartTimestamp() - 1 * 60 * 1000L; // 今天开始时间减去一分钟 作为最后结算时间
        Long validTime = DateUtils.getTodayStartTimestamp() - type * 24 * 60 * 60 * 1000L;
        StringBuilder shopSql = new StringBuilder();
        // 日结  三日结
        if (type == 1 || type == 3) {
            /**
             * SELECT
             *   O.agentId,
             *   O.shopId,
             *   OP.shopMoney,
             *   O.orderNo,
             *   O.totalPrice,
             *   O.deliveryPrice,
             *   O.originalYunfei,
             *   O.originalYunFeiShop,
             *   OP.platformAwardMoney,
             *   (IFNULL(OP.`cityAdminAward`,0)
             *   +IFNULL(OP.`cityAdminAward1`,0)
             *   +IFNULL(OP.`cityAdminAward2`,0)
             *   +IFNULL(OP.`cityAdminAward3`,0)
             *   +IFNULL(OP.`cityAdminAward4`,0)
             *  ) cityAdminAwardMoney,
             *   O.shopAwardMoney
             * FROM
             *   order_info AS O
             *   LEFT JOIN order_pay OP
             *     ON O.orderNo = OP.orderNo
             *     AND OP.delFlag = 0
             *   INNER JOIN
             *     (SELECT
             *       id AS shopId,
             *       shopName
             *     FROM
             *       shop_info
             *     WHERE delFlag = 0
             *       AND settlementType = 1
             *     UNION
             *     SELECT
             *       S.id AS shopId,
             *       S.shopName AS shopName
             *     FROM
             *       shop_info AS S
             *       LEFT JOIN shop_agent_relation AS SA
             *         ON S.id = SA.shopId
             *         AND SA.delFlag = 0
             *       LEFT JOIN city_agent AS C
             *         ON C.id = SA.agentId
             *         AND C.delFlag = 0
             *     WHERE S.delFlag = 0
             *       AND S.settlementType = 0
             *       AND C.settlementType = 1
             * ) AS T ON O.shopId = T.shopId
             *
             * WHERE O.delFlag = 0
             * AND OP.statisticState = 0
             * AND OP.settlementState = 0
             * AND OP.isOrderCancel = 0
             * AND O.isRefundApply != 2
             * AND O.orderStatus = 9 ;
             */
            shopSql.append(" SELECT id AS shopId, shopName   FROM shop_info  WHERE delFlag = 0 AND settlementType = ").append(type);
            if (isSysAuto) {
                shopSql.append(" and  (lastSettlementTime is null or lastSettlementTime < ").append(validTime).append(")");
            }
            shopSql.append(" UNION ")
                    .append("  SELECT S.id AS shopId,  S.shopName AS shopName FROM ").append(PojoTables.ShopInfo).append(" AS S LEFT JOIN ").append(PojoTables.SHOP_AGENT_RELATION).append(" AS SA ON S.id = SA.shopId AND SA.delFlag = 0 ")
                    .append("    LEFT JOIN city_agent AS C ").append(" ON C.id = SA.agentId  AND C.delFlag = 0 ")
                    .append(" WHERE S.delFlag = 0  AND S.settlementType = 0 AND C.settlementType = ").append(type);
            if (isSysAuto) {
                shopSql.append(" and  (S.lastSettlementTime is null or S.lastSettlementTime < ").append(validTime).append(")");
            }
        }

        // 周结算
        if (type == 7) {
            /**
             * SELECT
             *   O.agentId,
             *   O.shopId,
             *   OP.shopMoney,
             *   O.orderNo,
             *   O.totalPrice,
             *   O.deliveryPrice,
             *   O.originalYunfei,
             *   O.originalYunFeiShop,
             *   OP.platformAwardMoney,
             *   (
             *     IFNULL(OP.`cityAdminAward`, 0) + IFNULL(OP.`cityAdminAward1`, 0) + IFNULL(OP.`cityAdminAward2`, 0) + IFNULL(OP.`cityAdminAward3`, 0) + IFNULL(OP.`cityAdminAward4`, 0)
             *   ) cityAdminAwardMoney,
             *   (
             *     IFNULL(OP.`cityAdminMoney`, 0) + IFNULL(OP.`cityAdminMoney1`, 0) + IFNULL(OP.`cityAdminMoney2`, 0) + IFNULL(OP.`cityAdminMoney3`, 0) + IFNULL(OP.`cityAdminMoney4`, 0)
             *   ) cityAdminMoney,
             *   OP.platformMoney,
             *   OP.postmanMoney,
             *   OP.shopAwardMoney
             * FROM
             *   order_info AS O
             *   LEFT JOIN order_pay OP
             *     ON O.orderNo = OP.orderNo
             *     AND OP.delFlag = 0
             *   INNER JOIN
             *     ( SELECT
             *       DISTINCT(S.id) AS shopId
             *     FROM
             *       shop_info AS S
             *       LEFT JOIN shop_agent_relation AS SA
             *         ON S.id = SA.shopId
             *         AND SA.delFlag = 0
             *       LEFT JOIN city_agent AS C
             *         ON C.id = SA.agentId
             *         AND C.delFlag = 0
             *     WHERE S.delFlag = 0
             *       AND (S.settlementType = 7 OR (S.settlementType =0 AND C.settlementType = 7))
             *       AND C.settlementDay = 1
             *       ) AS T
             *     ON O.shopId = T.shopId
             * WHERE O.delFlag = 0
             *   AND OP.statisticState = 0
             *   AND OP.settlementState = 0
             *   AND OP.isOrderCancel = 0
             *   AND O.isRefundApply != 2
             *   AND (
             *     O.orderStatus = 9
             *     OR O.orderStatus = 29
             *     OR O.orderStatus = 64
             *     OR O.orderStatus = 19
             *   )
             *   AND O.createTime < 1606060740000
             */
            Calendar c = Calendar.getInstance();
            c.setTime(new Date());
            int weekday = c.get(Calendar.DAY_OF_WEEK);
            shopSql.append("  SELECT DISTINCT(S.id) AS shopId FROM ").append(PojoTables.ShopInfo).append(" AS S LEFT JOIN ").append(PojoTables.SHOP_AGENT_RELATION).append(" AS SA ON S.id = SA.shopId AND SA.delFlag = 0 ")
                    .append("    LEFT JOIN city_agent AS C ").append(" ON C.id = SA.agentId  AND C.delFlag = 0 ")
                    .append(" WHERE S.delFlag = 0   AND (S.settlementType = ").append(type).append(" OR (S.settlementType =0 AND C.settlementType = ").append(type).append(")) ");
            if (isSysAuto) {
                shopSql.append(" AND C.settlementDay = ").append(weekday);
            }

        }

        // 月结
        if (type == 30) {
            Calendar c = Calendar.getInstance();
            c.setTime(new Date());
            int monthDay = c.get(Calendar.DAY_OF_MONTH);
            shopSql.append("  SELECT DISTINCT(S.id) AS shopId FROM ").append(PojoTables.ShopInfo).append(" AS S LEFT JOIN ").append(PojoTables.SHOP_AGENT_RELATION).append(" AS SA ON S.id = SA.shopId AND SA.delFlag = 0 ")
                    .append("    LEFT JOIN city_agent AS C ").append(" ON C.id = SA.agentId  AND C.delFlag = 0 ")
                    .append(" WHERE S.delFlag = 0   AND (S.settlementType = ").append(type).append(" OR (S.settlementType =0 AND C.settlementType = ").append(type).append(")) ");
            if (isSysAuto) {
                shopSql.append(" AND C.settlementDay = ").append(monthDay);
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("  SELECT ")
                .append(" O.agentId,  O.shopId, OP.shopMoney, O.orderNo, O.totalPrice, O.deliveryPrice, O.originalYunfei, O.originalYunFeiShop, OP.platformAwardMoney,")
                .append(" (IFNULL(OP.`cityAdminAward`,0) +IFNULL(OP.`cityAdminAward1`,0) +IFNULL(OP.`cityAdminAward2`,0) +IFNULL(OP.`cityAdminAward3`,0) +IFNULL(OP.`cityAdminAward4`,0) ) cityAdminAwardMoney, ")
                .append(" (IFNULL(OP.`cityAdminMoney`, 0) + IFNULL(OP.`cityAdminMoney1`, 0) + IFNULL(OP.`cityAdminMoney2`, 0) + IFNULL(OP.`cityAdminMoney3`, 0) + IFNULL(OP.`cityAdminMoney4`, 0)) cityAdminMoney, ")
                .append("  OP.platformMoney, ")
                .append("  OP.postmanMoney, ")
                .append(" OP.shopAwardMoney ")
                .append(" FROM ")
                .append(PojoTables.OrderInfo).append(" as O ")
                .append(" LEFT JOIN ").append(PojoTables.OrderPay).append("  OP ")
                .append("  ON O.orderNo = OP.orderNo AND OP.delFlag = 0 ")
                .append(" INNER JOIN (").append(shopSql).append(") AS T ON O.shopId = T.shopId ")
                .append(" where O.delFlag = 0 ")
                .append(" AND OP.statisticState = 0 ")
                .append(" AND OP.settlementState = 0 ")
                .append(" AND OP.isOrderCancel = 0 ")
                .append(" AND O.isRefundApply != ").append(REFUND_APPLY_STATUS.PASS)
                .append(" AND (O.orderStatus = ").append(ORDER_STATUS.OVER)
                .append(" OR O.orderStatus = ").append(ORDER_STATUS.SHOP_FINISH)
                .append(" OR O.orderStatus = ").append(ORDER_STATUS.USER_SURED)
                .append(" OR O.orderStatus = ").append(ORDER_STATUS.POSTMAN_FINISH).append(") ")
                .append(" AND O.createTime < ").append(lastSettlementTime);

        List<OrderStatementInfo> orderStatementInfos = null;
        try {
            System.out.println("统计执行sql---->" + sql.toString());
            orderStatementInfos = (List<OrderStatementInfo>) UserDaoSql.getInstance().list(OrderStatementInfo.class, sql.toString());
        } catch (Exception e) {
            e.getMessage();
            return;
        }
        if (ValidateUtil.isAbsList(orderStatementInfos)) {
            collationAndCalculation(orderStatementInfos, type, lastSettlementTime, createTime, isSysAuto, opUser);
        }
    }

    // 分组  然后整理起来
    public static void collationAndCalculation(List<OrderStatementInfo> orderStatementInfos, int settlementType, Long lastSettlementTime, Long createTime, boolean isSysAuto, String opUser) throws SQLException {
        if (!ValidateUtil.isAbsList(orderStatementInfos)) {
            return;
        }
        // i 将单独的数据整理成组合的数据
        ConcurrentHashMap<Long, OrderStatementStatistics> map = new ConcurrentHashMap<>();
        orderStatementInfos.forEach(item -> {
            OrderStatementStatistics statementStatistics = map.get(item.getShopId());
            if (statementStatistics == null) {
                statementStatistics = new OrderStatementStatistics();
            }
            Set<String> orderNoSet = statementStatistics.getOrderNoSet();
            if (orderNoSet == null) {
                orderNoSet = new HashSet<String>();
            }
            if (!orderNoSet.contains(item.getOrderNo())) {
                orderNoSet.add(item.getOrderNo());
                statementStatistics.setOrderNoSet(orderNoSet);
                statementStatistics.setShopId(item.getShopId());
                if (statementStatistics.getAgentId() == null && ValidateUtil.isID(item.getAgentId())) {
                    statementStatistics.setAgentId(item.getAgentId());
                }
                statementStatistics.setTotalPrice(JYMathDoubleUtils.add(statementStatistics.getTotalPrice(), item.getTotalPrice()));
                statementStatistics.setDeliveryPrice(JYMathDoubleUtils.add(statementStatistics.getDeliveryPrice(), item.getDeliveryPrice()));
                statementStatistics.setOriginalYunfei(JYMathDoubleUtils.add(statementStatistics.getOriginalYunfei(), item.getOriginalYunfei()));
                statementStatistics.setOriginalYunFeiShop(JYMathDoubleUtils.add(statementStatistics.getOriginalYunFeiShop(), item.getOriginalYunFeiShop()));

                statementStatistics.setShopMoney(JYMathDoubleUtils.add(statementStatistics.getShopMoney(), item.getShopMoney()));
                statementStatistics.setPostmanMoney(JYMathDoubleUtils.add(statementStatistics.getPostmanMoney(), item.getPostmanMoney()));
                statementStatistics.setCityAdminMoney(JYMathDoubleUtils.add(statementStatistics.getCityAdminMoney(), item.getCityAdminMoney()));
                statementStatistics.setPlatformMoney(JYMathDoubleUtils.add(statementStatistics.getPlatformMoney(), item.getPlatformMoney()));

                statementStatistics.setPlatformAwardMoney(JYMathDoubleUtils.add(statementStatistics.getPlatformAwardMoney(), item.getPlatformAwardMoney()));
                statementStatistics.setShopAwardMoney(JYMathDoubleUtils.add(statementStatistics.getShopAwardMoney(), item.getShopAwardMoney()));
                statementStatistics.setCityAdminAwardMoney(JYMathDoubleUtils.add(statementStatistics.getCityAdminAwardMoney(), item.getCityAdminAwardMoney()));
                statementStatistics.setTotalCount(statementStatistics.getTotalCount() + 1);
                map.put(statementStatistics.getShopId(), statementStatistics);
            }
        });
        // ii 将整合的数据格式化
        List<OrderStatementStatistics> dataList = new ArrayList<>(map.values());
        Set<String> orderNoSet = new HashSet<>();
        dataList.forEach(item -> {
            item.setTotalPrice(JYMathDoubleUtils.scaleHalfUp(item.getTotalPrice()));
            item.setDeliveryPrice(JYMathDoubleUtils.scaleHalfUp(item.getDeliveryPrice()));
            item.setOriginalYunfei(JYMathDoubleUtils.scaleHalfUp(item.getOriginalYunfei()));
            item.setOriginalYunFeiShop(JYMathDoubleUtils.scaleHalfUp(item.getOriginalYunFeiShop()));
            item.setShopMoney(JYMathDoubleUtils.scaleHalfUp(item.getShopMoney()));
            item.setCityAdminMoney(JYMathDoubleUtils.scaleHalfUp(item.getCityAdminMoney()));
            item.setPlatformMoney(JYMathDoubleUtils.scaleHalfUp(item.getPlatformMoney()));
            item.setShopAwardMoney(JYMathDoubleUtils.scaleHalfUp(item.getShopAwardMoney()));
            item.setCityAdminAwardMoney(JYMathDoubleUtils.scaleHalfUp(item.getCityAdminAwardMoney()));
            item.setPlatformAwardMoney(JYMathDoubleUtils.scaleHalfUp(item.getPlatformAwardMoney()));
            item.setPostmanMoney(JYMathDoubleUtils.scaleHalfUp(item.getPostmanMoney()));
            item.setSettlementType(settlementType);
            item.setDelFlag(0).setStatementTime(lastSettlementTime).setCreateTime(createTime).setIsSysAutoStatistic(isSysAuto ? 1 : 0).setCreateUser(opUser).setOrderNos(StringUtils.join(item.getOrderNoSet(), ","));
            orderNoSet.addAll(item.getOrderNoSet());
        });
        // iii 批量插入数据db 并更新订单的统计状态 事务
        batchInsertDB(dataList, orderNoSet, lastSettlementTime);
    }

    // 插入并规整
    public static void batchInsertDB(List<OrderStatementStatistics> dataList, Set<String> orderSet, Long lastSettlementTime) throws SQLException {
        if (!ValidateUtil.isAbsList(dataList)) {
            return;
        }
        orderSet = orderSet.stream().map(item -> "'" + item + "'").collect(Collectors.toSet());
        Set<Long> shopIdSet = dataList.stream().map(item -> item.getShopId()).collect(Collectors.toSet());
        Connection conn = null;
        try {
            conn = UserDaoSql.getInstance().getConnection();
            conn.setAutoCommit(false);
            // 1.批量插入
            StringBuffer statementSb = new StringBuffer(" INSERT INTO ").append(PojoTables.ORDER_STATEMENT_STATISTICS)
                    .append(" (agentId,shopId,shopMoney,totalPrice,deliveryPrice,")   // 5
                    .append("originalYunfei,originalYunFeiShop,platformAwardMoney,cityAdminAwardMoney,shopAwardMoney,")  //5
                    .append("totalCount,statementState,statementTime,orderNos,isSysAutoStatistic,")  //5
                    .append("delFlag,createUser,createTime,settlementType,cityAdminMoney,")  //5
                    .append("platformMoney,postmanMoney)")  //5
                    .append("values(?,?,?,?,?,") // 5
                    .append("?,?,?,?,?,") // 5
                    .append("?,?,?,?,?,") // 5
                    .append("?,?,?,?,?,") // 5
                    .append("?,?)"); // 1
            PreparedStatement statementPre = null;
            statementPre = conn.prepareStatement(statementSb.toString());
            for (OrderStatementStatistics statementStatistics : dataList) {
                // 添加
                statementPre.setLong(1, statementStatistics.getAgentId());
                statementPre.setLong(2, statementStatistics.getShopId());
                statementPre.setDouble(3, statementStatistics.getShopMoney());
                statementPre.setDouble(4, statementStatistics.getTotalPrice());
                statementPre.setDouble(5, statementStatistics.getDeliveryPrice());

                statementPre.setDouble(6, statementStatistics.getOriginalYunfei());
                statementPre.setDouble(7, statementStatistics.getOriginalYunFeiShop());
                statementPre.setDouble(8, statementStatistics.getPlatformAwardMoney());
                statementPre.setDouble(9, statementStatistics.getCityAdminAwardMoney());
                statementPre.setDouble(10, statementStatistics.getShopAwardMoney());

                statementPre.setInt(11, statementStatistics.getTotalCount());
                statementPre.setInt(12, statementStatistics.getStatementState());
                statementPre.setLong(13, statementStatistics.getStatementTime());
                statementPre.setString(14, statementStatistics.getOrderNos());
                statementPre.setInt(15, statementStatistics.getIsSysAutoStatistic());

                statementPre.setInt(16, statementStatistics.getDelFlag());
                statementPre.setString(17, statementStatistics.getCreateUser());
                statementPre.setLong(18, statementStatistics.getCreateTime());
                statementPre.setInt(19, statementStatistics.getSettlementType());
                statementPre.setDouble(20, statementStatistics.getCityAdminMoney());
                statementPre.setDouble(21, statementStatistics.getPlatformMoney());
                statementPre.setDouble(22, statementStatistics.getPostmanMoney());
                statementPre.addBatch();
            }
            statementPre.executeBatch();
            // 2. 更新统计状态
            StringBuilder updateSql = new StringBuilder();
            updateSql.append(" update ").append(PojoTables.OrderPay)
                    .append(" set statisticState = 1 ").append(" where delFlag = 0 and statisticState = 0 ")
                    .append(" and orderNo  in (").append(StringUtils.join(orderSet, ",")).append(") ");
            UserDaoSql.getInstance().executeBySql(updateSql.toString(), conn);
            //3. 更新店铺的最后核算日
            if (shopIdSet != null && !shopIdSet.isEmpty()) {
                StringBuilder shopSql = new StringBuilder();
                shopSql.append(" update ").append(PojoTables.ShopInfo).append(" set lastSettlementTime = ").append(lastSettlementTime)
                        .append(" where delFlag  = 0 and id in(").append(StringUtils.join(shopIdSet, ",")).append(")");
                UserDaoSql.getInstance().executeBySql(shopSql.toString(), conn);
            }
            conn.commit();
        } catch (Exception e) {
            e.getMessage();
            UserDaoSql.getInstance().rollback(conn);
            throw e;
        } finally {
            UserDaoSql.getInstance().close(conn);
        }
        // iii 更新缓存
        if (shopIdSet != null && !shopIdSet.isEmpty()) {
            ShopMemory.getInstance().modify(StringUtils.join(shopIdSet, ","));
        }
        OrderPayMemory.getInstance().run();
        UserWalletFlowMemory.getInstance().run();
    }


}
